from py2neo import Graph,Node,Relationship,NodeMatcher,RelationshipMatcher
from db_utils.mysql_utils import MysqlInstance

# 定义三个label
company_label = "企业"
project_label = "工程项目"
project_major_label = "项目负责人"
sale_label = "销售"
project_stage_label = "工程阶段"


default_connect = {
    "host":'127.0.0.1',
    'username':"root",
    'password':'123456',
    "database":"demo_knowbase",
}


default_connect['host'] = '192.168.2.181'
default_connect['password'] = 'MyNewPass4!'


class createBHPData(object):
    def __init__(self):
        # 建立连接
        link = Graph("http://localhost:7474", username="neo4j", password="123456")
        self.graph = link
        self.matcher = NodeMatcher(graph=self.graph)
        self.relation_matcher = RelationshipMatcher(graph=self.graph)
        self.mysql_obj = MysqlInstance(default_connect)

    def clean_node(self):
        # 清空数据库
        self.graph.delete_all()

    def create_node(self):
        company_infos = self.mysql_obj.search_info_from_mysql('company',{})
        for company_info in company_infos:
            company_node = Node(company_label,**company_info)
            self.graph.create(company_node)

        zhongnenglianhe_node = Node(company_label,name="众能联合")
        self.graph.create(zhongnenglianhe_node)

        project_infos = self.mysql_obj.search_info_from_mysql('project',{})
        for project_info in project_infos:
            project_node = Node(project_label, **project_info)
            self.graph.create(project_node)

        sale_infos = self.mysql_obj.search_info_from_mysql('sale',{})
        for sale_info in sale_infos:
            sale_node = Node(sale_label, **sale_info)
            self.graph.create(sale_node)


        project_stage_infos = self.mysql_obj.search_info_from_mysql("project_stage",{})
        for project_stage_info in project_stage_infos:
            project_stage_info_node = Node(project_stage_label,**project_stage_info)
            self.graph.create(project_stage_info_node)

        project_major_infos = self.mysql_obj.search_info_from_mysql("project_major", {})
        for project_major_info in project_major_infos:
            project_major_info_node = Node(project_major_label, **project_major_info)
            self.graph.create(project_major_info_node)

    def create_Rel(self):
        """
        建立关系双向关系定义
        :return:
        """


        # # 工程之间的相似度
        # similary_project_sql = '''select p.name as project_1 ,p2.name as project_2 from similary_project sp join project p on p.project_id = sp.project_id join project p2 on p2.project_id = sp.similary_project_id'''
        # self.mysql_obj.cur.execute(similary_project_sql)
        # result = self.mysql_obj.cur.fetchall()
        # for item in result:
        #     project_1 = item['project_1']
        #     project_2 = item['project_2']
        #     project_1_node = self.matcher.match(project_label,name=project_1).first()
        #     project_2_node = self.matcher.match(project_label,name=project_2).first()
        #
        #     relation = self.relation_matcher.match((project_1_node,project_2_node),r_type="相似工程").first()
        #     relation_propety = {"相似度":0.8}
        #     if not relation:
        #         tnb_to_cli = Relationship(project_1_node, '相似工程', project_2_node,**relation_propety)
        #         self.graph.create(tnb_to_cli)
        #
        #     relation = self.relation_matcher.match({project_2_node,project_1_node},r_type="相似工程").first()
        #     if not relation:
        #         tnb_to_cli = Relationship(project_2_node, '相似工程', project_1_node,**relation_propety)
        #         self.graph.create(tnb_to_cli)


        # 项目经理和公司的关系
        company_and_project_major_sql = '''select c2.name  as company_name,pm.name  as project_major_name from company c2 join relation_company_project_major rcpm  on c2.company_id  = rcpm.company_id  join project_major pm on pm.project_major_id =rcpm .project_major_id '''
        self.mysql_obj.cur.execute(company_and_project_major_sql)
        result = self.mysql_obj.cur.fetchall()
        for item in result:
            company_name = item['company_name']
            project_major_name = item['project_major_name']
            company_node = self.matcher.match(company_label, name=company_name).first()
            project_major_node = self.matcher.match(project_major_label, name=project_major_name).first()

            relation = self.relation_matcher.match((project_major_node, company_node),r_type='就职').first()
            if not relation:
                tnb_to_cli = Relationship(project_major_node, '就职', company_node)
                self.graph.create(tnb_to_cli)

            relation = self.relation_matcher.match((company_node, project_major_node),r_type='项目经理').first()
            if not relation:
                tnb_to_cli = Relationship(company_node, '项目经理', project_major_node )
                self.graph.create(tnb_to_cli)

        # 工程项目和公司以及项目负责人关系
        print("start")
        project_company_and_project_major_sql = '''select  p.name as "project_name" , c.name as "company_name" ,pm.name as "project_major_name",rcp .winbidding as "是否中标" from relation_company_project rcp join company c  on c.company_id = rcp.company_id join project p on p.project_id =rcp.project_id join project_major pm  on pm.project_major_id = rcp.project_major_id '''
        self.mysql_obj.cur.execute(project_company_and_project_major_sql)
        result = self.mysql_obj.cur.fetchall()
        for item in result:
            project_name = item['project_name']
            company_name = item['company_name']
            project_major_name = item['project_major_name']
            is_win_bidding = item['是否中标']

            project_node = self.matcher.match(project_label, name=project_name).first()
            company_node = self.matcher.match(company_label, name=company_name).first()
            project_major_node = self.matcher.match(project_major_label, name=project_major_name).first()

            if int(is_win_bidding) == 0:
                relation = self.relation_matcher.match({project_node, company_node},r_type='投标单位').first()
                if not relation:
                    tnb_to_cli = Relationship(project_node, '投标单位', company_node)
                    self.graph.create(tnb_to_cli)

                relation = self.relation_matcher.match({company_node,project_node}, r_type='投标').first()
                if not relation:
                    tnb_to_cli = Relationship(company_node, '投标', project_node)
                    self.graph.create(tnb_to_cli)

                relation = self.relation_matcher.match({project_node, project_major_node}, r_type='投标单位负责人').first()
                if not relation:
                    tnb_to_cli = Relationship(project_node, '投标单位负责人', project_major_node)
                    self.graph.create(tnb_to_cli)

            else:
                relation = self.relation_matcher.match({project_node, company_node}, r_type='中标单位').first()
                if not relation:
                    tnb_to_cli = Relationship(project_node, '中标单位', company_node)
                    self.graph.create(tnb_to_cli)

                relation = self.relation_matcher.match({company_node, project_node}, r_type='投标').first()
                if not relation:
                    tnb_to_cli = Relationship(company_node, '施工', project_node)
                    self.graph.create(tnb_to_cli)

                relation = self.relation_matcher.match({project_node, project_major_node}, r_type='中标单位负责人').first()
                if not relation:
                    tnb_to_cli = Relationship(project_node, '中标单位负责人', project_major_node)
                    self.graph.create(tnb_to_cli)

                # relation = self.relation_matcher.match({project_major_node,project_node}, r_type='负责项目').first()
                # if not relation:
                #     tnb_to_cli = Relationship(project_major_node, '负责项目', project_node)
                #     self.graph.create(tnb_to_cli)

        # # 销售和项目负责人的关系
        # sale_and_project_major_sql = '''select pm.name as project_major_name ,sale.name  as sale_name from relation_project_major_sale rpms join sale on sale.sale_id = rpms .sale_id  join project_major pm on pm.project_major_id = rpms .project_major_id '''
        # self.mysql_obj.cur.execute(sale_and_project_major_sql)
        # result = self.mysql_obj.cur.fetchall()
        # for item in result:
        #     sale_name = item['sale_name']
        #     project_major_name = item['project_major_name']
        #     sale_name = self.matcher.match(sale_label, name=sale_name).first()
        #     project_major_node = self.matcher.match(project_major_label, name=project_major_name).first()
        #
        #     relation = self.relation_matcher.match((sale_name,project_major_node), r_type='客户').first()
        #     if not relation:
        #         tnb_to_cli = Relationship(sale_name, '客户',project_major_node)
        #         self.graph.create(tnb_to_cli)
        #
        #     # relation = self.relation_matcher.match((sale_name, project_major_node), r_type='租赁商').first()
        #     # if not relation:
        #     #     tnb_to_cli = Relationship(sale_name, '租赁商', project_major_node)
        #     #     self.graph.create(tnb_to_cli)
        #
        # # 项目和项目阶段关系映射
        # stage_and_project_sql = '''select ps.阶段顺序 ,p.name,ps.project_stage_id,ps.project_id from project_stage ps join project p  on ps.project_id = p.project_id'''
        # self.mysql_obj.cur.execute(stage_and_project_sql)
        # result = self.mysql_obj.cur.fetchall()
        # for item in result:
        #     stage_index = item['阶段顺序']
        #     project_name = item['name']
        #
        #     stage_search = {
        #         'project_stage_id':item['project_stage_id']
        #     }
        #     project_stage_node = self.matcher.match(project_stage_label, **stage_search).first()
        #     project_node = self.matcher.match(project_label, name=project_name).first()
        #
        #     relation = self.relation_matcher.match((project_node, project_stage_node), r_type='施工阶段').first()
        #     if not relation:
        #         tnb_to_cli = Relationship(project_node, '施工阶段', project_stage_node)
        #         self.graph.create(tnb_to_cli)


        # # 项目负责人和项目负责人关系映射
        # project_major_and_project_major_sql = '''select pm.name as project_major_one ,pm2.name as project_major_two from  relation_project_worktogether rpw join project_major pm on rpw.project_major_id = pm.project_major_id  join project_major pm2 on pm2.project_major_id =rpw.worktogether_major_id  '''
        # self.mysql_obj.cur.execute(project_major_and_project_major_sql)
        # result = self.mysql_obj.cur.fetchall()
        # for item in result:
        #     project_major_one = item['project_major_one']
        #     project_major_two = item['project_major_two']
        #
        #
        #     project_major_one_node = self.matcher.match(project_major_label, name=project_major_one).first()
        #     project_major_two_node = self.matcher.match(project_major_label, name=project_major_two).first()
        #
        #     relation = self.relation_matcher.match((project_major_one_node, project_major_two_node), r_type='工程合作').first()
        #     if not relation:
        #         tnb_to_cli = Relationship(project_major_one_node, '工程合作', project_major_two_node)
        #         self.graph.create(tnb_to_cli)
        #
        #     relation = self.relation_matcher.match((project_major_two_node,project_major_one_node),
        #                                            r_type='工程合作').first()
        #     if not relation:
        #         tnb_to_cli = Relationship(project_major_two_node, '工程合作', project_major_one_node)
        #         self.graph.create(tnb_to_cli)

        # sale_infos = self.mysql_obj.search_info_from_mysql('sale',{})
        # for sale_info in sale_infos:
        #     sale_node = self.matcher.match(sale_label, **sale_info).first()
        #     company_node = self.matcher.match(company_label, name="众能联合").first()
        #     relation = self.relation_matcher.match((company_node,sale_node),
        #                                            r_type='员工').first()
        #     if not relation:
        #         tnb_to_cli = Relationship(company_node, '员工', sale_node)
        #         self.graph.create(tnb_to_cli)

    def test(self):
        sale_infos = self.mysql_obj.search_info_from_mysql('sale', {})
        for sale_info in sale_infos:
            sale_node = self.matcher.match(sale_label, **sale_info).first()
            company_node = self.matcher.match(company_label, name="众能联合").first()
            relation = self.relation_matcher.match((company_node, sale_node),
                                                   r_type='员工').first()
            if not relation:
                tnb_to_cli = Relationship(company_node, '员工', sale_node)
                self.graph.create(tnb_to_cli)


c = createBHPData()
#c.test()
# c.clean_node()
c.create_node()
c.create_Rel()